/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.seclust;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author baga
 */
public class DBOperator {

    private String userName;
    private String password;
    private String DBName;
    Connection conn;
    private String contents[];
    private String urls[];
    private ArrayList contentVec;
    private ArrayList urlVec;

    public DBOperator(String userName, String password, String DBName) {
        this.userName = userName;
        this.password = password;
        this.DBName = DBName;

        String connStr = "jdbc:mysql://localhost:3306/"
                + this.DBName + "?user=" + this.userName
                + "&password=" + this.password;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            try {
                conn = DriverManager.getConnection(connStr);
            } catch (SQLException se) {
                se.printStackTrace();
            }
        } catch (ClassNotFoundException ce) {
            ce.printStackTrace();
        }
    }

    public DBOperator() {
        this("root", "root", "db_secluts");
    }

    public void insTable(String body, String url, String content, String title) {
        try {
            String queryCheck = "select count(*) banyak from t_clust "
                    + "where url = ?";
            PreparedStatement stmCheck = conn.prepareCall(queryCheck);
            stmCheck.setString(1, url);
            ResultSet checkResult = stmCheck.executeQuery();
            int res = 0;
            while (checkResult.next()) {
                res = checkResult.getInt(1);
            }
            if (res == 0) {
                String queryShow = "select max(t_clust_id) as maxis from t_clust";
                PreparedStatement stmt = conn.prepareStatement(queryShow);
                ResultSet rs = stmt.executeQuery();
                int nilai = 0;
                while (rs.next()) {
                    nilai = rs.getInt("maxis");
                }

                String querBld = "insert into t_clust values(?, ?, ?, ?, ?)";
                PreparedStatement prStmt = conn.prepareCall(querBld);

                prStmt.setInt(1, nilai + 1);
                prStmt.setString(2, body);
                prStmt.setString(3, url);
                prStmt.setString(4, content);
                prStmt.setString(5, title);

                prStmt.execute();
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    public void insTableFull(String body, String url, String content, String title) {
        try {
            String queryCheck = "select count(*) banyak from t_clust_full "
                    + "where url = ?";
            PreparedStatement stmCheck = conn.prepareCall(queryCheck);
            stmCheck.setString(1, url);
            ResultSet checkResult = stmCheck.executeQuery();
            int res = 0;
            while (checkResult.next()) {
                res = checkResult.getInt(1);
            }
            if (res == 0) {
                String queryShow = "select max(t_clust_full_id) as maxis from t_clust_full";
                PreparedStatement stmt = conn.prepareStatement(queryShow);
                ResultSet rs = stmt.executeQuery();
                int nilai = 0;
                while (rs.next()) {
                    nilai = rs.getInt("maxis");
                }

                String querBld = "insert into t_clust_full values(?, ?, ?, ?, ?)";
                PreparedStatement prStmt = conn.prepareCall(querBld);

                prStmt.setInt(1, nilai + 1);
                prStmt.setString(4, body);
                prStmt.setString(2, url);
                prStmt.setString(3, content);
                prStmt.setString(5, title);

                prStmt.execute();
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    public void getDocBaseIndex(String title, String table) {
        String url = "";
        String parameter = title.toUpperCase();
        if (table.equals("tbta_pa")) {
            url = "select url_jurnal url, abstract content from tbta_pa where " +
                    "upper(abstract) like ? order by doc_id";
            parameter = "%"+parameter+"%";
        } else {
            url = "select " + table + "_id, url, content from "
                    + table + " where upper(title) = ? order by " + table + "_id asc";
        }
        contentVec = new ArrayList();
        urlVec = new ArrayList();

        try {
            PreparedStatement pstm = conn.prepareCall(url);
            pstm.setString(1, parameter);
            System.out.println("query " + url);
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
//                System.out.println("ini idnya " + rs.getInt(1));
                urlVec.add(rs.getString("url"));
                contentVec.add(rs.getString("content"));
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    public ArrayList getStopWords() {
        ArrayList al = new ArrayList();
        try {
            PreparedStatement ps = conn.prepareStatement("select words from "
                    + "stopwords");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String tmp = rs.getString(1);
                al.add(tmp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBOperator.class.getName()).log(Level.SEVERE, null, ex);
        }
        return al;
    }

    public String[] getContents() {
        String tempStr[] = new String[contentVec.size()];
        return (String[]) contentVec.toArray(tempStr);
    }

    public String[] getUrls() {
        String tempStr[] = new String[urlVec.size()];
        return (String[]) urlVec.toArray(tempStr);
    }
//    public static void main(String[] args) {
//        DBOperator dbOp = new DBOperator();
//        dbOp.getDocBaseIndex("book");
//        String[] avb = dbOp.getContents();
//        for (String tmp : avb) {
//            System.out.println(tmp);
//        }
//
//        //dbOp.insTable("isi", "isi", "isi", "isi");
//    }
}
